<?php
namespace Common\Model;
use Think\Model;

/** 导出结算单
 * Class BillsExportModel
 * @package Common\Model
 */
class BillsExportModel extends Model {

    public $id;

    public function exportExcel()
    {
        $id = $this->id;
        if (!$id) return false;

        //获取数据
        //汇总信息
        $info = M('b2c_bills_account')->where(['id' => $id])->find();
        //$this->assign('info', $info);
        //入账信息
        $order_list = M('b2c_bills_account_orderitem')->where(['account_id' => $info['account_id']])->select();
        //退款信息
        $refund_list = M('b2c_bills_account_refunditem')->where(['account_id' => $info['account_id']])->select();
        $payments = M('b2c_payment')->getField('name', true);
        if ($order_list) {
            $order = [];
            $tmp = [];
            //订单号 商品 佣金率 抽佣金额 物流费用 订单金额 门店优惠 实支付金额 支付日期 支付方式 线上手续费
            foreach ($order_list as $key => $item) {
                $order[$key]['id'] = $key + 1;//订单id
                $order[$key]['order_id'] = ' '.$item['order_id'];//订单id
                $order[$key]['name'] = $item['name'];
                $order[$key]['yc_rate'] = $item['yc_rate'].'%';
                $order[$key]['yc_fee'] = $item['yc_fee'];
                $order[$key]['off_price'] = $item['off_price'];
                $order[$key]['cost_item'] = $item['order_price'];//订单价格
                $order[$key]['cost_freight'] = $item['cost_freight'];//物流费用
                $order[$key]['total_amount'] = $item['pay_price'];//实际支付价格
                $order[$key]['use_bonus'] = $item['use_bonus'];//使用奖金
                $order[$key]['createtime'] = date('Y-m-d H:i:s', $item['createtime']);//支付日期
                if ($item['paymethod_id']) {
                    $order[$key]['paymethod_name'] = $payments[$item['paymethod_id'] - 1];//支付方式
                } else {
                    $order[$key]['paymethod_name'] = '';
                }
            }
            sort($order);
        }
        if ($refund_list) {
            $refund = [];
            $tmp = [];
            //订单号 商品 佣金率 抽佣金额 退款金额  退款完成日期 门店经办 退款经办
            foreach ($refund_list as $key => $item) {
                //序 订单号  商品  佣金率  抽佣金额  退款金额  退款完成日期 门店经办  退款经办  操作

                $refund[$key]['id'] = $key + 1;//订单id
                $refund[$key]['order_id'] = ' '.$item['order_id'];//订单id
                $refund[$key]['name'] = $item['name'];
                $refund[$key]['yc_rate'] = $item['yc_rate'].'%';
                $refund[$key]['yc_fee'] = $item['yc_fee'];
                $refund[$key]['price_detail'] = $item['price_detail'];//退款金额
                $refund[$key]['refund_bonus'] = $item['refund_bonus'];//退款金额
                $refund[$key]['finance_confirm_t'] = date('Y-m-d H:i:s', $item['createtime']);//退款完成日期
                $refund[$key]['store_name'] = $item['store_user'];
                $refund[$key]['finance_name'] = $item['finance_user'];//退款经办
            }
            sort($refund);
        }
        //得到数据 $order $refund $info 组装成
        //id 订单号 商品 佣金率 抽佣金额 物流费用 订单金额 门店优惠 实支付金额 支付日期 支付方式 线上手续费
        $expCellName = [
            ['0', 'ID', 'id'], ['1', '订单号', 'order_id'], ['2', '商品', 'name'], ['3', '佣金率', 'yc_rate'], ['4', '抽佣金额', 'yc_fee'], ['5', '门店优惠', 'off_price'], ['6', '订单金额', 'cost_item'], ['7', '物流费用', 'cost_freight'], ['8', '实支付金额', 'total_amount'],['9', '使用奖金金额', 'use_bonus'], ['10', '支付日期', 'createtime'], ['11', '支付方式', 'paymethod_name'],
        ];
        $expCellName2 = [
            ['0', 'ID', 'id'], ['1', '订单号', 'order_id'], ['2', '商品', 'name'], ['3', '佣金率', 'yc_rate'], ['4', '抽佣金额', 'yc_fee'], ['5', '退款金额', 'price_detail'], ['6', '返还奖金金额', 'refund_bonus'],['7', '退款完成日期', 'finance_confirm_t'], ['8', '门店经办', 'store_name'], ['9', '退款经办', 'finance_name']
        ];
        $expTableData = $order;
        //dump($refund);return;
        $this->excel2($expCellName, $order, $expCellName2, $refund);
        exit;
    }


    public function excel2($expCellName, $expTableData, $expCellName2, $expTableData2)
    {

        $xlsTitle = iconv('utf-8', 'gb2312', '怡臣电子商务结算单');//文件名称
        $fileName = date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $cellNum2 = count($expCellName2);
        $dataNum2 = count($expTableData2);
        Vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();

        $this->array_key_simple($objPHPExcel,$expTableData);

        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');//合并单元格
//        $objPHPExcel->getActiveSheet()->mergeCells('H7:H11');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle . '  导出时间:' . date('Y-m-d H:i:s'));
        if ($dataNum > 0) {
            for ($i = 0; $i < $cellNum; $i++) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
            }

            //逻辑区
            for ($i = 0; $i < $dataNum; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][2]]);
                }
            }
        }


        if ($dataNum2 > 0) {
            $skip = $dataNum + 5;
            for ($i = 0; $i < $cellNum; $i++) {
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . $skip, $expCellName2[$i][1]);
            }
            for ($i = 0; $i < $dataNum; $i++) {
                for ($j = 0; $j < $cellNum; $j++) {
                    $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + $skip + 1), $expTableData2[$i][$expCellName2[$j][2]]);
                }
            }

        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    public function array_key_simple($objPHPExcel, $order){
//        var_dump($order);
        $order_id_array = array_column($order, 'order_id');
//        var_dump($order_id_array);
        $names = array_count_values($order_id_array);
//        var_dump($names);
//        var_dump(array_keys($names));
//        var_dump(array_values($names));

        $order_key = array_keys($names);
        $order_values = array_values($names);

//        array_search('h',$array);

        $sum = count($order_id_array);
        for ($i=0; $i<$sum; $i++) {

            if ($order_values[$i] > 1) {
                $a = array_search($order_key[$i],$order_id_array);
//                var_dump($order_key[$i].'--'.$a.'--'.$order_values[$i]);
//                var_dump($order_key[$i].'--'.($a + 3).'--'.($a + 2 + $order_values[$i]));

                $objPHPExcel->getActiveSheet()->mergeCells('H' . ($a + 3).':H'.($a + 2 + $order_values[$i]));
                $objPHPExcel->getActiveSheet()->mergeCells('B' . ($a + 3).':B'.($a + 2 + $order_values[$i]));
            }



        }

//        exit;
    }


    public function view()
    {

        $id = $this->id;
        //汇总信息
        $info = [];
        $info = M('b2c_bills_account')->where(['id' => $id])->find();
        //$this->assign('info', $info);
        //入账信息
        $order_list = M('b2c_bills_account_orderitem')->where(['account_id' => $info['account_id']])->select();
        //退款信息
        $refund_list = M('b2c_bills_account_refunditem')->where(['account_id' => $info['account_id']])->select();
        $payments = M('b2c_payment')->getField('name', true);
        $order = [];
        if ($order_list) {
            $tmp = [];
            /**
             * CREATE TABLE `sdb_b2c_bills_account_orderitem` (
             * `item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '结算单详情主键id',
             * `account_id` bigint(15) NOT NULL DEFAULT '0' COMMENT '结算单id',
             * `order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '订单id',
             * `name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名称',
             * `yc_rate` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '佣金率',
             * `yc_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '抽佣金额',
             * `cost_freight` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '物流费用',
             * `order_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '订单金额',
             * `off_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '店铺优惠',
             * `pay_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '实际支付',
             * `createtime` int(10) NOT NULL DEFAULT '0' COMMENT '支付日期',
             * `paymethod_id` tinyint(1) NOT NULL DEFAULT '0' COMMENT '支付方式',
             * `bandle_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '线上手续费',
             * PRIMARY KEY (`item_id`)
             * ) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8;
             */
            //订单号 商品 佣金率 抽佣金额 物流费用 订单金额 店铺优惠 实支付金额 支付日期 支付方式 线上手续费
            foreach ($order_list as $item) {
                $order[$item['order_id']]['order_id'] = $item['order_id'];//订单id
                $order[$item['order_id']]['total_amount'] = $item['pay_price'];//实际支付价格
                $order[$item['order_id']]['use_bonus'] = $item['use_bonus'];//使用奖金
                $order[$item['order_id']]['cost_freight'] = $item['cost_freight'];//物流费用
                $order[$item['order_id']]['cost_item'] = $item['order_price'];//订单价格
                $order[$item['order_id']]['createtime'] = $item['createtime'];//支付日期
                $order[$item['order_id']]['paymethod_id'] = $item['paymethod_id'];//支付方式
                $order[$item['order_id']]['nickname'] = $item['nickname'];//会员昵称
                $order[$item['order_id']]['ship_name'] = $item['ship_name'];//收货人
                if ($item['paymethod_id']) {
                    $order[$item['order_id']]['paymethod_name'] = $payments[$item['paymethod_id'] - 1];//支付方式
                } else {
                    $order[$item['order_id']]['paymethod_name'] = '';
                }
                $order[$item['order_id']]['list'] = [];
                $ii = [
                    'name' => $item['name'],//货品名称
                    //'nums' => $item['nums'],//数量
                    'yc_rate' => $item['yc_rate'],
                    'yc_fee' => $item['yc_fee'],
                    'youhui' => $item['off_price'],
                ];
                $tmp[$item['order_id']][] = $ii;
                $order[$item['order_id']]['list'] = $tmp[$item['order_id']];
                $order[$item['order_id']]['geshu'] = count($order[$item['order_id']]['list']);
                $order[$item['order_id']]['yongjin'] = array_sum(array_column($tmp[$item['order_id']], 'yc_fee'));
            }
            sort($order);
            //$this->assign('order', $order);
        }
        $refund = [];
        if ($refund_list) {
            $tmp = [];
            /**
             * CREATE TABLE `sdb_b2c_bills_account_refunditem` (
             * `item_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '结算单详情主键id',
             * `account_id` bigint(15) NOT NULL DEFAULT '0' COMMENT '结算单id',
             * `order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '订单id',
             * `name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名称',
             * `yc_rate` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '佣金率',
             * `yc_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '抽佣金额',
             * `price_detail` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '退款金额',
             * `createtime` int(10) NOT NULL DEFAULT '0' COMMENT '退款完成时间',
             * `store_user` varchar(50) NOT NULL DEFAULT '' COMMENT '店铺经办人名称',
             * `store_uid` int(11) NOT NULL DEFAULT '0' COMMENT '店铺经办人id',
             * `finance_user` varchar(50) NOT NULL DEFAULT '' COMMENT '店铺经办人名称',
             * `finance_uid` int(11) NOT NULL DEFAULT '0' COMMENT '平台经办人id',
             * PRIMARY KEY (`item_id`)
             * ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
             */
            //订单号 商品 佣金率 抽佣金额 退款金额  退款完成日期 店铺经办 退款经办
            foreach ($refund_list as $item) {
                //序 订单号  商品  佣金率  抽佣金额  退款金额  退款完成日期 店铺经办  退款经办  操作
                $refund[$item['order_id']]['order_id'] = $item['order_id'];//订单id
                $refund[$item['order_id']]['price_detail'] = $item['price_detail'];//退款金额
                $refund[$item['order_id']]['refund_bonus'] = $item['refund_bonus'];//返还奖金
                $refund[$item['order_id']]['finance_confirm_t'] = $item['createtime'];//退款完成日期
                $refund[$item['order_id']]['finance_name'] = $item['finance_user'];//退款经办
                $refund[$item['order_id']]['store_name'] = $item['store_user'];
                $refund[$item['order_id']]['list'] = [];
                $ii = [
                    'name' => $item['name'],//货品名称
                    //'nums' => $item['nums'],//数量
                    'yc_rate' => $item['yc_rate'],
                    'yc_fee' => $item['yc_fee'],
                ];
                $tmp[$item['order_id']][] = $ii;
                $refund[$item['order_id']]['list'] = $tmp[$item['order_id']];
                $refund[$item['order_id']]['geshu'] = count($refund[$item['order_id']]['list']);
                $refund[$item['order_id']]['yongjin'] = array_sum(array_column($tmp[$item['order_id']], 'yc_fee'));
            }
            sort($refund);
            //$this->assign('refund', $refund);
        }
        $cost_freight = 0;
        foreach ($order as $v){
            $cost_freight += $v['cost_freight'];
        }
        return [
            'info' => $info,
            'order' => $order,
            'refund' => $refund,
            'cost_freight' =>$cost_freight
        ];
    }
}